﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LumenWorks.Framework.IO.Csv;
using System.IO; 
using LumenWorks.Framework.Tests.Unit.IO.Csv;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Adex;
using MediaMonitor.Common;
using System.Transactions;

using System.Xml;
using System.Xml.Linq;
using iTextSharp.text;
using iTextSharp.text.html;
using iTextSharp.text.pdf;
using iTextSharp.text.xml;
using iTextSharp.text.html.simpleparser;
using System.Web.UI.HtmlControls;

using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Net;
using System.Configuration;
using Telerik.Web.UI;
using System.Collections;
using System.Drawing;
using SubSonic;


namespace MediaMonitor.Admin
{
    public partial class RF_Upload2 : Adex.BasePage
    {
        DateTime sDate = DateTime.MinValue, eDate = DateTime.MinValue;

        private static string AppName = ConfigurationManager.AppSettings["AppName"].ToString();
        private static string AdminEmail = ConfigurationManager.AppSettings["AdminEmail"].ToString();
        private iTextSharp.text.Font PDF_FONT_DEFAULT;
        private iTextSharp.text.Font PDF_FONT_TH;
        private iTextSharp.text.Font PDF_FONT_BOLD;
        private iTextSharp.text.Font PDF_FONT_GRAY;
        private iTextSharp.text.Font PDF_FONT_LIGHTGRAY;
        private iTextSharp.text.Font PDF_FONT_TITLE;
        private iTextSharp.text.Font PDF_FONT_HEADING;
        private iTextSharp.text.Font PDF_FONT_HEADING_GRAY;
        private iTextSharp.text.Font PDF_FONT_BIG;
        private iTextSharp.text.Font PDF_FONT_BIG_BOLD;
        private iTextSharp.text.Font PDF_FONT_NAV;
        private iTextSharp.text.Font PDF_FONT_GridElements;

        protected void Page_Init(object sender, EventArgs e)
        {
            BaseFont bf = BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, false);
            string fontpath = Server.MapPath("../");
            BaseFont customfont = BaseFont.CreateFont(fontpath + "Fonts/KOKILA.TTF", BaseFont.CP1252, BaseFont.EMBEDDED);
            iTextSharp.text.Font Kokila = new iTextSharp.text.Font(customfont, 44, iTextSharp.text.Font.BOLD, BaseColor.BLACK);
            PDF_FONT_TITLE = Kokila;
            BaseFont customfontarial = BaseFont.CreateFont(fontpath + "Fonts/ARIAL.TTF", BaseFont.CP1252, BaseFont.EMBEDDED);
            iTextSharp.text.Font ARIAL = new iTextSharp.text.Font(customfontarial, 11, iTextSharp.text.Font.BOLD, BaseColor.BLACK);
            iTextSharp.text.Font ARIAL11 = new iTextSharp.text.Font(customfontarial, 11, iTextSharp.text.Font.BOLD, BaseColor.BLACK);
            iTextSharp.text.Font ARIALNORMAL11 = new iTextSharp.text.Font(customfontarial, 11, iTextSharp.text.Font.NORMAL, BaseColor.BLACK);
            iTextSharp.text.Font ARIALNORMAL7 = new iTextSharp.text.Font(customfontarial, 7, iTextSharp.text.Font.NORMAL, BaseColor.BLACK);
            PDF_FONT_DEFAULT = ARIALNORMAL11;
            PDF_FONT_TH = ARIAL;
            PDF_FONT_BOLD = ARIAL11;
            PDF_FONT_GRAY = new iTextSharp.text.Font(bf, 14, iTextSharp.text.Font.NORMAL, BaseColor.DARK_GRAY);
            PDF_FONT_LIGHTGRAY = new iTextSharp.text.Font(bf, 15, iTextSharp.text.Font.NORMAL, new BaseColor(System.Drawing.Color.FromArgb(141, 141, 141)));
            PDF_FONT_HEADING = new iTextSharp.text.Font(bf, 16, iTextSharp.text.Font.BOLD, BaseColor.WHITE);
            PDF_FONT_HEADING_GRAY = new iTextSharp.text.Font(bf, 18, iTextSharp.text.Font.BOLD, BaseColor.GRAY);
            PDF_FONT_BIG = new iTextSharp.text.Font(bf, 14, iTextSharp.text.Font.NORMAL, BaseColor.BLACK);
            PDF_FONT_BIG_BOLD = new iTextSharp.text.Font(bf, 14, iTextSharp.text.Font.BOLD, BaseColor.BLACK);
            PDF_FONT_NAV = new iTextSharp.text.Font(bf, 14, iTextSharp.text.Font.BOLD, BaseColor.RED);
            PDF_FONT_GridElements = ARIALNORMAL7;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            //if (flAjaxUploader.IsPosting)
            //    this.ManagePost();
        }

        private void ManagePost()
        {
            //HttpPostedFileAJAX pf = flAjaxUploader.PostedFile;

            ////if (pf.ContentType.Equals("image/gif") && pf.ContentLength <= 5 * 1024)
            //    flAjaxUploader.SaveAs("~/temp", pf.FileName);
        }

        protected void btnUpload_Click(Object sender, EventArgs e)
        {

            string errMsg1, errMsg2, errMsg3, errMsg4, errMsg5, errMsg6, errMsg7;
            errMsg1 = errMsg2 = errMsg3 = errMsg4 = errMsg5 = errMsg6 = errMsg7 = "";


            if (flRF1.PostedFile.FileName.Length > 0)
            {
                lblInfo1.ForeColor = UploadRF(flRF1, out errMsg1, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo1.Text = errMsg1;
            }
            else
                lblInfo1.Text = "No file selected to upload";


            if (flRF2.PostedFile.FileName.Length > 0)
            {
                lblInfo2.ForeColor = UploadRF(flRF2, out errMsg2,chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo2.Text = errMsg2;
            }
            else
                lblInfo2.Text = "No file selected to upload";

            if (flRF3.PostedFile.FileName.Length > 0)
            {
                lblInfo3.ForeColor = UploadRF(flRF3, out errMsg3, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo3.Text = errMsg3;
            }
            else
                lblInfo3.Text = "No file selected to upload";

            if (flRF4.PostedFile.FileName.Length > 0)
            {
                lblInfo4.ForeColor = UploadRF(flRF4, out errMsg4, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo4.Text = errMsg4;
            }
            else
                lblInfo4.Text = "No file selected to upload";

            if (flRF5.PostedFile.FileName.Length > 0)
            {
                lblInfo5.ForeColor = UploadRF(flRF5, out errMsg5, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo5.Text = errMsg5;
            }
            else
                lblInfo5.Text = "No file selected to upload";

            if (flRF6.PostedFile.FileName.Length > 0)
            {
                lblInfo6.ForeColor = UploadRF(flRF6, out errMsg6, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo6.Text = errMsg6;
            }
            else
                lblInfo6.Text = "No file selected to upload";

            if (flRF7.PostedFile.FileName.Length > 0)
            {
                lblInfo7.ForeColor = UploadRF(flRF7, out errMsg7, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo7.Text = errMsg7;
            }
            else
                lblInfo7.Text = "No file selected to upload";


            pnlInfo1.Visible = pnlInfo2.Visible = pnlInfo3.Visible = pnlInfo4.Visible = pnlInfo5.Visible = pnlInfo6.Visible = pnlInfo7.Visible = true;

            pnlUpload.Visible = false;


        }

        private bool UploadRF(FileUpload flRF, out string errMsg, bool priliminaryData)
        {
            int countryID = MediaMonitor.Common.AppUser.GetCountry();
            StreamReader readFile = new StreamReader(flRF.PostedFile.InputStream);
            CsvReader csv = new CsvReader(new StringReader(readFile.ReadToEnd()), false, ',', '"', '\\', '☺', ValueTrimmingOptions.All); // for comments char = 12
            string date = "";
            while (csv.ReadNextRecord())
            {
                date = csv[1].ToString();
                break;
            }
            string countedRecord= SPs.CountRecords(date).ExecuteScalar().ToString();
            if (Convert.ToInt64(countedRecord) != 0)
            {
                errMsg = "No file uploaded";
                string script = "alert(\"Record for this date already exist. please delete them first!\");";
                ScriptManager.RegisterStartupScript(this, this.GetType(), "ServerControlScript", script, true);
                return false;
            }
            else
            {
                string path = Server.MapPath("~/Files") + "//" + flRF.FileName;
                flRF.SaveAs(path);
                string id=SPs.SpBulkInsertCommercialLog(path, priliminaryData, countryID).ExecuteScalar().ToString();
                File.Delete(path);
                errMsg = "<h2>Data Uploaded Successfully</h2>All data from RF file successfully uploaded";
                //if (id != "")
               // {
                    GetPDF(date);
                //}
                return true;
            }
        }

        private System.Data.DataTable GetRFDataTable()
        {
            DataTable dt = new DataTable();
            //// dt = Adex.Rf.Query().WHERE("ID", 0).ExecuteDataSet().Tables[0];
            dt.Columns.Add("ID", System.Type.GetType("System.Int32"));//0
            dt.Columns.Add("MediaCode", System.Type.GetType("System.Int32"));//1
            dt.Columns.Add("RFDate", System.Type.GetType("System.DateTime"));//2
            dt.Columns.Add("TimeStart", System.Type.GetType("System.Int32"));//3
            dt.Columns.Add("TimeEnd", System.Type.GetType("System.Int32"));//4
            dt.Columns.Add("Duration", System.Type.GetType("System.Int32"));//5
            dt.Columns.Add("ProgramCode", System.Type.GetType("System.Int32"));//6
            dt.Columns.Add("ProgramName", System.Type.GetType("System.String"));//7
            dt.Columns.Add("Typology", System.Type.GetType("System.String"));//8
            dt.Columns.Add("NominalCost", System.Type.GetType("System.Decimal"));//9
            dt.Columns.Add("RealCost", System.Type.GetType("System.Decimal"));//10

            dt.Columns.Add("PositionInBreak", System.Type.GetType("System.Int32"));//11
            dt.Columns.Add("NumberOfSpotsInBreak", System.Type.GetType("System.Int32"));//12
            dt.Columns.Add("TypeOfSpot", System.Type.GetType("System.Int32"));//13
            dt.Columns.Add("TypeOfBuyCode", System.Type.GetType("System.Int32"));//14
            dt.Columns.Add("TypeOfBuyName", System.Type.GetType("System.String"));//15

            dt.Columns.Add("ProdVersionCode", System.Type.GetType("System.Int32"));//16
            dt.Columns.Add("ProdVersionName", System.Type.GetType("System.String"));//17
            dt.Columns.Add("ProductCode", System.Type.GetType("System.Int32"));//18
            dt.Columns.Add("ProductName", System.Type.GetType("System.String"));//19

            dt.Columns.Add("Constant1", System.Type.GetType("System.String"));//20
            dt.Columns.Add("Constant2", System.Type.GetType("System.String"));//21

            dt.Columns.Add("CategoryCode", System.Type.GetType("System.Int32"));//22
            dt.Columns.Add("CategoryName", System.Type.GetType("System.String"));//23

            dt.Columns.Add("SubSectorCode", System.Type.GetType("System.Int32"));//24
            dt.Columns.Add("SubSectorName", System.Type.GetType("System.String"));//25

            dt.Columns.Add("SectorCode", System.Type.GetType("System.Int32"));//26
            dt.Columns.Add("SectorName", System.Type.GetType("System.String"));//27

            dt.Columns.Add("Constant3", System.Type.GetType("System.String"));//28

            dt.Columns.Add("BrandCode", System.Type.GetType("System.Int32"));//29
            dt.Columns.Add("BrandName", System.Type.GetType("System.String"));//30

            dt.Columns.Add("AdvertiserCode", System.Type.GetType("System.Int32"));//31
            dt.Columns.Add("AdvertiserName", System.Type.GetType("System.String"));//32

            dt.Columns.Add("Constant4", System.Type.GetType("System.String"));//33

            dt.Columns.Add("AgencyCode", System.Type.GetType("System.Int32"));//34
            dt.Columns.Add("AgencyName", System.Type.GetType("System.String"));//35
            dt.Columns.Add("mediaTypeCode", System.Type.GetType("System.Int32"));//36
            dt.Columns.Add("mediaType", System.Type.GetType("System.String"));//37
            dt.Columns.Add("TimeStartFormatted", System.Type.GetType("System.String"));//38
            dt.Columns.Add("TimeEndFormatted", System.Type.GetType("System.String"));//39
            dt.Columns.Add("CountryID", System.Type.GetType("System.Int32"));//40
            dt.Columns.Add("PriliminaryData", System.Type.GetType("System.Boolean"));//41
            
            return dt;

        }
        private void GetPDF(string date)
        {

            String Users = String.Empty;


            if (chkSendToSpecificClients.Checked)
            {
                if (radGrid.SelectedItems.Count < 1)
                {
                    string script = "alert(\"No Company selected!\");";
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "ServerControlScript", script, true);
                }
                else
                {
                    foreach (GridDataItem item in radGrid.SelectedItems)
                    {

                        VSiteUserCollection CompanyUsers = new Select().From(VSiteUser.Schema).Where("CompanyCode").In(Convert.ToInt32(item.GetDataKeyValue("CompanyCode").ToString())).ExecuteAsCollection<VSiteUserCollection>(); ;
                        String UserEmailAddress = string.Empty;
                        foreach (VSiteUser user in CompanyUsers)
                        {
                            UserEmailAddress += user.Email + ",";
                        }
                        //UserEmailAddress = "niteshguleria@nascenture.com";
                        GetCSVDetail(Convert.ToInt32(item.GetDataKeyValue("CompanyCode").ToString()), UserEmailAddress, date);
                    }
                }
            }
            else
            {

                DataTable dtCompanies = Company.Query().DISTINCT().SetSelectList("CompanyCode").WHERE("NewCommerceMail", true).ExecuteDataSet().Tables[0];
                if (dtCompanies.Rows.Count > 0) //If Companies Exist in Database
                {

                    Int32 CompanyCode;

                    //Loop of Companies
                    foreach (DataRow dr in dtCompanies.Rows)
                    {
                        CompanyCode = Convert.ToInt32(dr["CompanyCode"].ToString());

                        DataTable dtUsers = VSiteUser.Query().DISTINCT().SetSelectList("Email").WHERE("RoleName", "user").AND("CompanyCode", CompanyCode).ExecuteDataSet().Tables[0];
                        String UserEmails = String.Empty;
                        //Loop For Users of Particular Companies
                        foreach (DataRow dr1 in dtUsers.Rows)
                        {
                            UserEmails += dr1["Email"].ToString() + ",";

                        }
                        //UserEmails = "niteshguleria@nascenture.com";


                        //GetCSVDetail(CompanyCode, UserEmails, date);
                            GetPDFDetail(CompanyCode, UserEmails, date);
                        //Send Mail to the Users of Particular Company
                    }


                    string script = "alert(\"pdf sent successfully!\");";
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "ServerControlScript", script, true);

                }

                else
                {
                    string script = "alert(\"No Record Exists!\");";
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "ServerControlScript", script, true);

                }
            }
        }


        private void GetPDFDetail(Int32 CompanyID, String UserEmails,string date)
        {
            DataSet dsCommercialPDF = new Common.ReportFuncs().GetReportNewCommercialAllPDF(Convert.ToInt32(CompanyID), date, date,false);
            if ((dsCommercialPDF.Tables[0].Rows.Count > 0) || (dsCommercialPDF.Tables[1].Rows.Count > 0) || (dsCommercialPDF.Tables[2].Rows.Count > 0))
            {
                DataTable dtTV = dsCommercialPDF.Tables[0];
                DataTable dtRadio = dsCommercialPDF.Tables[1];
                DataTable dtPress = dsCommercialPDF.Tables[2];
                if ((dsCommercialPDF.Tables[0].Rows.Count > 0))
                 dtTV = RemoveDuplicateRows(dsCommercialPDF.Tables[0], "Product");
                if ((dsCommercialPDF.Tables[1].Rows.Count > 0))
                 dtRadio = RemoveDuplicateRows(dsCommercialPDF.Tables[1], "Product");
                if ((dsCommercialPDF.Tables[2].Rows.Count > 0))
                 dtPress = RemoveDuplicateRows(dsCommercialPDF.Tables[2], "Product");

                //  Company company = new Company(hdnCompanyID.Value);
                Company company = new Company(CompanyID); //(not required) Template is change according to Category of Users.
                string pdfImg1 = "firstpage.jpg";
                string pdfImg2 = "secondpage.jpg";
                //By Default for New Commercial , there is only one template for all users
                string homeImageFilePath = Server.MapPath("~") + "\\images\\newcommercialtemplate\\" + pdfImg1;
                string subImageFilePath = Server.MapPath("~") + "\\images\\newcommercialtemplate\\" + pdfImg2;
                // PDF GENERATEION - start

                iTextSharp.text.Image bgHomePage = iTextSharp.text.Image.GetInstance(homeImageFilePath);
                iTextSharp.text.Image bgSubPage = iTextSharp.text.Image.GetInstance(subImageFilePath);
                iTextSharp.text.Rectangle PdfPageSize = new iTextSharp.text.Rectangle(bgHomePage.Width, bgHomePage.Height);
                iTextSharp.text.Document pdfDoc = new iTextSharp.text.Document(PdfPageSize);

                bgHomePage.Alignment = iTextSharp.text.Image.WRITABLE_DIRECT;
                bgSubPage.Alignment = iTextSharp.text.Image.WRITABLE_DIRECT;

                bgHomePage.ScaleToFit(PdfPageSize.Width, PdfPageSize.Height);
                bgSubPage.ScaleToFit(PdfPageSize.Width, PdfPageSize.Height);

                bgHomePage.SetAbsolutePosition(0, 0);
                bgSubPage.SetAbsolutePosition(0, 0);

                MemoryStream memoryStream = new MemoryStream();
                PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);

                // Our custom Header and Footer is done using Event Handler
                TwoColumnHeaderFooter PageEventHandler = new TwoColumnHeaderFooter();
                writer.PageEvent = PageEventHandler;

                // Define the page header
                pdfDoc.Open();
                CreatePDFPage_Home(pdfDoc, writer, bgHomePage, "NEW COMMERCIALS");
                CreatePDFPage_Criteria(pdfDoc, writer, bgSubPage, Utility.ToDateTime(Utility.GetDateFromNumber(date.ToString())), Utility.ToDateTime(Utility.GetDateFromNumber(date.ToString())));
                CreatePDFPage_TVList(pdfDoc, writer, bgSubPage, dtTV);
                CreatePDFPage_RADIOList(pdfDoc, writer, bgSubPage, dtRadio);
                CreatePDFPage_PRESSList(pdfDoc, writer, bgSubPage, dtPress);
                //Keeps the memoryStream object open when closing the Document (doc)
                writer.CloseStream = false;
                pdfDoc.Close();

                //Moves the pointer to the beginning of the stream. Without this
                //line an empty file is generated and attached to the email.
                memoryStream.Position = 0;

                SendMail(memoryStream, UserEmails); // this is commented to not send Testing mails to client.


                // PDF GENERATION - end
            }

        }

        public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
        {
            Hashtable hTable = new Hashtable();
            ArrayList duplicateList = new ArrayList();

            //Add list of all the unique item value to hashtable, which stores combination of key, value pair.
            //And add duplicate item value in arraylist.
            foreach (DataRow drow in dTable.Rows)
            {
                if (hTable.Contains(drow[colName]))
                    duplicateList.Add(drow);
                else
                    hTable.Add(drow[colName], string.Empty);
            }

            //Removing a list of duplicate items from datatable.
            foreach (DataRow dRow in duplicateList)
                dTable.Rows.Remove(dRow);

            //Datatable which contains unique records will be return as output.
            return dTable;
        }
        private void SendMail(MemoryStream memoryStream, String Users)
        {
            String toAccount = String.Empty;
            String msg = "These are New Commercial";
            String title = AppName + " New Commercial";
            toAccount = Users;
            //toAccount = "niteshguleria@nascenture.com";
            String MyHost = ConfigurationManager.AppSettings["SMTPHost"].ToString();
            String MySMTPPort = ConfigurationManager.AppSettings["SMTPPort"].ToString();
            String MySMTPSSL = ConfigurationManager.AppSettings["SMTPSSL"].ToString();

            String fromAddr = AdminEmail;   //Email Address of Sender
            //String fromAddr="niteshguleria@nascenture.com";
            MailMessage smtpMssg = new MailMessage();
            smtpMssg.From = new MailAddress(fromAddr);

            if (toAccount.Contains(","))
            {
                string[] tos = toAccount.Split(',');
                for (int i = 0; i < tos.Length; i++)
                {
                    if (tos[i] == "," || tos[i] == "")
                    {
                    }
                    else
                    {
                        smtpMssg.To.Add(new MailAddress(tos[i].Replace(",", "")));
                    }
                }
            }
            else
            {
                smtpMssg.To.Add(new MailAddress(toAccount));
            }

            smtpMssg.Subject = title;
            smtpMssg.Body = msg;
            smtpMssg.IsBodyHtml = true;

            smtpMssg.Attachments.Add(new Attachment(memoryStream, "NewCommercial.pdf"));
           
            System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient(MyHost, Convert.ToInt32(MySMTPPort));
            smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
            if (MySMTPSSL == "true")
            {
                smtpClient.EnableSsl = true;
            }

            try
            {
                smtpClient.Send(smtpMssg);

            }
            catch (Exception ex)
            {

                throw new Exception("Email not sent | Reason: [" + ex.Message + "]");

            }
        }
        private void SendMail(string fileName,String Users)
        {
            String toAccount = String.Empty;
            String msg = "These are New Commercial";
            String title = AppName + " New Commercial";
            toAccount = Users;
            //toAccount = "niteshguleria@nascenture.com";
            String MyHost = ConfigurationManager.AppSettings["SMTPHost"].ToString();
            String MySMTPPort = ConfigurationManager.AppSettings["SMTPPort"].ToString();
            String MySMTPSSL = ConfigurationManager.AppSettings["SMTPSSL"].ToString();

            String fromAddr = AdminEmail;   //Email Address of Sender
            //String fromAddr="niteshguleria@nascenture.com";
            MailMessage smtpMssg = new MailMessage();
            smtpMssg.From = new MailAddress(fromAddr);

            if (toAccount.Contains(","))
            {
                string[] tos = toAccount.Split(',');
                for (int i = 0; i < tos.Length; i++)
                {
                    if (tos[i] == "," || tos[i] == "")
                    {
                    }
                    else
                    {
                        smtpMssg.To.Add(new MailAddress(tos[i].Replace(",", "")));
                    }
                }
            }
            else
            {
                smtpMssg.To.Add(new MailAddress(toAccount));
            }

            smtpMssg.Subject = title;
            smtpMssg.Body = msg;
            smtpMssg.IsBodyHtml = true;
            System.Net.Mail.Attachment attachment;
            attachment = new System.Net.Mail.Attachment(Server.MapPath(fileName));
            smtpMssg.Attachments.Add(attachment);

            System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient(MyHost, Convert.ToInt32(MySMTPPort));
            smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
            if (MySMTPSSL == "true")
            {
                smtpClient.EnableSsl = true;
            }

            try
            {
                smtpClient.Send(smtpMssg);

            }
            catch (Exception ex)
            {

                throw new Exception("Email not sent | Reason: [" + ex.Message + "]");

            }
           
        }

        private void CreatePDFPage_Home(Document pdfDoc, PdfWriter writer, iTextSharp.text.Image bgImage, string title)
        {
            pdfDoc.NewPage();
            PdfContentByte pcb = writer.DirectContentUnder;
            pcb.AddImage(bgImage);
            Paragraph pTitle = new Paragraph(44.0f, "\n\n\n\n\n" + title, PDF_FONT_TITLE);
            pTitle.Alignment = iTextSharp.text.Rectangle.ALIGN_CENTER;
            Paragraph pDate = new Paragraph(18.0f, "\n\n\n\n\n\n\n\n\n\n\n\n\n" + DateTime.Now.ToString("dd MMMM yyyy"), PDF_FONT_HEADING_GRAY);
            pdfDoc.Add(pTitle);
            pdfDoc.Add(pDate);

        }

        private void CreatePDFPage_Criteria(Document pdfDoc, PdfWriter writer, iTextSharp.text.Image bgImage, DateTime Date, DateTime Date1)
        {
            pdfDoc.NewPage();
            PdfContentByte pcb1 = writer.DirectContentUnder;
            pcb1.AddImage(bgImage);
            Paragraph pPageHeading = new Paragraph(14.0f, "\n\n\nNEW COMMERCIALS", PDF_FONT_HEADING);
            pPageHeading.Alignment = iTextSharp.text.Element.ALIGN_LEFT;
            pPageHeading.IndentationLeft = 330f;
            pPageHeading.SpacingAfter = 16f;
            pdfDoc.Add(pPageHeading);



            Paragraph pCriteria = new Paragraph();
            pCriteria.Add(new Chunk("\n\n\n\n\n\n\n\n\n\n\n\n\n\n New Commercials", PDF_FONT_BIG_BOLD));
            pCriteria.Alignment = iTextSharp.text.Element.ALIGN_CENTER;
            pdfDoc.Add(pCriteria);

            Paragraph pDate = new Paragraph("From: " + Date.ToString("dd MMMM, yyyy") + "\n To: " + Date1.ToString("dd MMMM, yyyy"), PDF_FONT_LIGHTGRAY);
            pDate.Alignment = iTextSharp.text.Element.ALIGN_LEFT;
            pDate.IndentationLeft = 330f;

            pdfDoc.Add(pDate);

        }

        private void CreatePDFPage_TVList(Document pdfDoc, PdfWriter writer, iTextSharp.text.Image bgImage, DataTable dtTV)
        {

            if (dtTV.Rows.Count > 0)
            {
                pdfDoc.NewPage();
                PdfContentByte pcb1 = writer.DirectContentUnder;
                pcb1.AddImage(bgImage);
                Paragraph pPageHeading = new Paragraph(14.0f, "\n\n\nTV\n\n\n\n", PDF_FONT_HEADING);
                pPageHeading.Alignment = iTextSharp.text.Element.ALIGN_LEFT;
                pPageHeading.IndentationLeft = 330f;
                pdfDoc.Add(pPageHeading);
                Anchor prodAnchor = new Anchor(".");
                prodAnchor.Name = "TV";
                Paragraph p = new Paragraph();
                p.Add(prodAnchor);
                pdfDoc.Add(p);
                PdfPTable prodTable = GetPDFTable(dtTV);
                pdfDoc.Add(prodTable);

            }

        }

        private void CreatePDFPage_RADIOList(Document pdfDoc, PdfWriter writer, iTextSharp.text.Image bgImage, DataTable dtRadio)
        {

            if (dtRadio.Rows.Count > 0)
            {
                pdfDoc.NewPage();
                PdfContentByte pcb1 = writer.DirectContentUnder;
                pcb1.AddImage(bgImage);
                Paragraph pPageHeading = new Paragraph(14.0f, "\n\n\nRADIO\n\n\n\n", PDF_FONT_HEADING);
                pPageHeading.Alignment = iTextSharp.text.Element.ALIGN_LEFT;
                pPageHeading.IndentationLeft = 330f;
                pdfDoc.Add(pPageHeading);
                Anchor prodAnchor = new Anchor(".");
                prodAnchor.Name = "Radio";
                Paragraph p = new Paragraph();
                p.Add(prodAnchor);
                pdfDoc.Add(p);
                PdfPTable prodTable = GetPDFTable(dtRadio);
                pdfDoc.Add(prodTable);

            }

        }

        private void CreatePDFPage_PRESSList(Document pdfDoc, PdfWriter writer, iTextSharp.text.Image bgImage, DataTable dtPress)
        {

            if (dtPress.Rows.Count > 0)
            {
                pdfDoc.NewPage();
                PdfContentByte pcb1 = writer.DirectContentUnder;
                pcb1.AddImage(bgImage);
                Paragraph pPageHeading = new Paragraph(14.0f, "\n\n\nPRESS\n\n\n\n", PDF_FONT_HEADING);
                pPageHeading.Alignment = iTextSharp.text.Element.ALIGN_LEFT;
                pPageHeading.IndentationLeft = 330f;
                pdfDoc.Add(pPageHeading);
                Anchor prodAnchor = new Anchor(".");
                prodAnchor.Name = "Press";
                Paragraph p = new Paragraph();
                p.Add(prodAnchor);
                pdfDoc.Add(p);
                PdfPTable prodTable = GetPDFTable(dtPress);
                pdfDoc.Add(prodTable);

            }

        }

        private PdfPTable GetPDFTable(DataTable dt)
        {
            if (dt.Rows.Count == 0)
                return new PdfPTable(0);

            int columnCount = dt.Columns.Count;
            int rowCount = dt.Rows.Count;
            int tableRows = rowCount + 3;
            iTextSharp.text.pdf.PdfPTable gridTable = new PdfPTable(columnCount);
            gridTable.WidthPercentage = 100;
            var colWidthPercentages = new[] { 7f, 10f, 10f, 20f, 7f, 15f, 20f, 7f, 2f };
            gridTable.SetWidths(colWidthPercentages);
            //add headers
            PdfPCell[] arrTh = new PdfPCell[columnCount];
            //Header Bind Start
            for (int i = 1; i < columnCount; i++)
            {

                string header = "";
                string[] heads = SubSonic.Sugar.Strings.ToWords(dt.Columns[i].ColumnName);
                foreach (string head in heads)
                    header += head + " ";


                arrTh[i - 1] = new PdfPCell();
                arrTh[i - 1].AddElement(new Chunk(header, PDF_FONT_BOLD));
                arrTh[i - 1].Border = 0;
                arrTh[i - 1].BorderColorLeft = BaseColor.WHITE;
                arrTh[i - 1].BorderWidthTop = 1f;
                arrTh[i - 1].BorderWidthBottom = 1f;
                if (i == 1)
                {
                    arrTh[i - 1].BorderWidthLeft = 1f;
                    arrTh[i - 1].BorderColorLeft = BaseColor.BLACK;
                    arrTh[i - 1].PaddingLeft = 5f;
                }
                if (i == columnCount - 1)
                {
                    arrTh[i - 1].BorderWidthRight = 1f;
                    arrTh[i - 1].BorderColorRight = BaseColor.BLACK;
                    arrTh[i - 1].Colspan = 2;
                }
                arrTh[i - 1].PaddingTop = 10f;
                arrTh[i - 1].PaddingBottom = 20f;
            }
            //Header Bind End
            arrTh[columnCount - 1] = new PdfPCell();
            gridTable.Rows.Add(new PdfPRow(arrTh));
            int rowIndex = 0;

            //Inner Elements Bind Start
            foreach (DataRow dr in dt.Rows)
            {
                PdfPCell[] arrTd = new PdfPCell[columnCount];
                int colIndex = 1;
                for (colIndex = 1; colIndex < columnCount; colIndex++)
                {
                    if (colIndex == columnCount - 1)
                    {

                        arrTd[colIndex - 1] = new PdfPCell();
                        Chunk cImage = new Chunk("PREVIEW", PDF_FONT_GridElements);
                        Chunk cDownload = new Chunk("/Download", PDF_FONT_GridElements);
                        String Url = String.Empty;
                        String FileToDownlod = String.Empty;

                        if (AppUser.GetCountry() == 58)
                        {
                            Url = "http://www.mediamonitor.do/pdf-popup.aspx?id=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                            FileToDownlod = "http://www.mediamonitor.do/pdf-popup.aspx?FTD=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                        }
                        else
                        {
                            Url = "http://www.mediamonitor.com.tt/pdf-popup.aspx?id=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                            FileToDownlod = "http://www.mediamonitor.com.tt/pdf-popup.aspx?FTD=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                        }
                        //  Url = "http://localhost:5919/MediaMonitor/pdf-popup.aspx?id=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                        //FileToDownlod = "http://localhost:5919/MediaMonitor/pdf-popup.aspx?FTD=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                        cImage.SetAction(PdfAction.JavaScript("app.launchURL(\'" + Url + "\',true);", null));
                        cDownload.SetAction(PdfAction.JavaScript("app.launchURL(\'" + FileToDownlod + "\',true);", null));
                        arrTd[colIndex - 1].AddElement(cImage);
                        arrTd[colIndex - 1].AddElement(cDownload);
                        arrTd[colIndex - 1].Border = 0;
                        //String Fullurl = Server.MapPath("~") + "\\NewCommercials\\" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex], "");

                        //cImage.SetAction(PdfAction.JavaScript("app.launchURL(\'www.google.com\',true);", null)); 
                        //cImage.SetAction(PdfAction.JavaScript("app.launchURL(\'www.google.com\',true);", null)); 
                        // cImage.SetAction(PdfAction.JavaScript("app.alert('JS says')",null));
                        //cImage.SetAction(PdfAction.JavaScript("app.window.open('"  + Fullurl + "','_blank')", null));
                        //String StrDet= "<script type='text/javascript'>window.open('" + Fullurl + "','_blank');</script>";
                        //detail.Reference = Fullurl;


                        //cImage.SetAction(PdfAction.JavaScript("app.media.openPlayer({ alerter: null });", null));

                        //Anchor detail = new Anchor(cImage);
                        //detail.Reference = "http://localhost:5919/MediaMonitor/admin/pdf-popup.aspx?id=" + Utility.IsNullStr(dt.Rows[rowIndex][colIndex]);
                        //Paragraph p1 = new Paragraph();
                        //p1.Alignment = Rectangle.ALIGN_TOP;
                        //p1.Add(detail);

                    }
                    else
                    {
                        arrTd[colIndex - 1] = new PdfPCell();
                        arrTd[colIndex - 1].AddElement(new Chunk(Utility.IsNullStr(dt.Rows[rowIndex][colIndex], ""), PDF_FONT_GridElements));
                        arrTd[colIndex - 1].Border = 0;
                    }
                }

                arrTd[colIndex - 1] = new PdfPCell();

                arrTd[colIndex - 1].Border = 0;
                arrTd[colIndex - 1].PaddingTop = 10f;

                gridTable.Rows.Add(new PdfPRow(arrTd));
                rowIndex++;
            }
            //Inner Elements Bind End

            return gridTable;
        }



        // Additional method for sending csv files
        protected void radGrid_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
        {

            DataTable dt = Company.Query().SetSelectList("*, CONVERT(VARCHAR, DateAdded, 101) AS LastModifiedDate, CONVERT(VARCHAR, ValidTo, 101) AS ValidToDate, CONVERT(VARCHAR, ValidFrom, 101) AS ValidFromDate").ORDER_BY("CompanyName").ExecuteDataSet().Tables[0];
            radGrid.DataSource = dt;

        }

       
        private void GetCSVDetail(Int32 CompanyID, String UserEmails, string date)
        {
            DataSet dsCommercialPDF = new Common.ReportFuncs().GetReportNewCommercialAllPDF(Convert.ToInt32(CompanyID), date, date,true);
            if ((dsCommercialPDF.Tables[0].Rows.Count > 0) || (dsCommercialPDF.Tables[1].Rows.Count > 0) || (dsCommercialPDF.Tables[2].Rows.Count > 0))
            {
                DataTable dtTV = dsCommercialPDF.Tables[0];
                DataTable dtRadio = dsCommercialPDF.Tables[1];
                DataTable dtPress = dsCommercialPDF.Tables[2];
                if ((dsCommercialPDF.Tables[0].Rows.Count > 0))
                    dtTV = RemoveDuplicateRows(dsCommercialPDF.Tables[0], "Product");
                if ((dsCommercialPDF.Tables[1].Rows.Count > 0))
                    dtRadio = RemoveDuplicateRows(dsCommercialPDF.Tables[1], "Product");
                if ((dsCommercialPDF.Tables[2].Rows.Count > 0))
                    dtPress = RemoveDuplicateRows(dsCommercialPDF.Tables[2], "Product");

                var result = new StringBuilder();
                for (int i = 0; i < dtTV.Columns.Count; i++)
                {
                    result.Append(dtTV.Columns[i].ColumnName);
                    result.Append(i == dtTV.Columns.Count - 1 ? "\n" : ",");
                }

                foreach (DataRow row in dtTV.Rows)
                {
                    for (int i = 0; i < dtTV.Columns.Count; i++)
                    {
                        result.Append(row[i].ToString());
                        result.Append(i == dtTV.Columns.Count - 1 ? "\n" : ",");
                    }
                }

                
                for (int row = 1; row < dtRadio.Rows.Count;row++ )
                {
                    for (int i = 0; i < dtRadio.Columns.Count; i++)
                    {
                        result.Append(dtRadio.Rows[row][i].ToString());
                        result.Append(i == dtRadio.Columns.Count - 1 ? "\n" : ",");
                    }
                }

               

                for (int row = 1; row < dtPress.Rows.Count; row++)
                {
                    for (int i = 0; i < dtPress.Columns.Count; i++)
                    {
                        result.Append(dtPress.Rows[row][i].ToString());
                        result.Append(i == dtPress.Columns.Count - 1 ? "\n" : ",");
                    }
                }
                string fileName = CompanyID.ToString() + "Commercial.csv";
                File.WriteAllText(Server.MapPath(fileName), result.ToString());
                //  Company company = new Company(hdnCompanyID.Value);
               

                SendMail(CompanyID.ToString()+ "Commercial.csv",UserEmails); // this is commented to not send Testing mails to client.

                //File.Delete(Server.MapPath(fileName));
                // PDF GENERATION - end
            }

        }

        
    }
}